package com.sinopac.model.CustomerApply;

import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import netfusion.jbranch.database.DataAccessManager;

import com.lowagie.text.BadElementException;
import com.lowagie.text.Cell;
import com.lowagie.text.Document;
import com.lowagie.text.Element;
import com.lowagie.text.Font;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Phrase;
import com.lowagie.text.Rectangle;
import com.lowagie.text.Table;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.pdf.PdfWriter;
import com.sinopac.entity.CustomerApply.CustomerVO;
import com.sinopac.entity.CustomerApply.ReportVO;
import com.sinopac.model.core.ModelCore;
import com.sinopac.util.IdUtil;
import com.sinopac.util.PropsUtil;

public class CustomerApplyModel extends ModelCore{
	
	/**
	 * 取得主畫面所需資料
	 * @param customer_id
	 * @return
	 */
	public List<CustomerVO> getCustomerForm(String customer_id){
		DataAccessManager dam = new DataAccessManager();
		List<CustomerVO> customerList = new ArrayList<CustomerVO>();
		CustomerVO customerVO = new CustomerVO();
		Map<String,String> otpStatus = new HashMap<String, String>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		otpStatus.put("00", "未啟用");
		otpStatus.put("01", "正常");
		otpStatus.put("02", "已暫禁");
		otpStatus.put("03", "已註銷");
		try {
			String sql = "select * from Customers where customer_id = :customer_id";
			dam.setObject("customer_id", customer_id);
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQueryMap();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
//					System.out.println("token_esn:token_esn=" + (String)map.get("token_esn"));
					customerVO.setCustomer_id((String)map.get("customer_id"));
					// demoReport專用
					String strId = map.get("customer_id").toString();
					String demoId = strId.replace(strId.substring(4,18), "XXXXX");
//					System.out.println("strId;" + demoId);
					customerVO.setCustomer_demo_id(demoId);
					
					customerVO.setCustomer_name((String)map.get("customer_name"));
					customerVO.setCustomer_phone((String)map.get("customer_phone"));
					customerVO.setAo_code((String)map.get("AO_code"));
					customerVO.setCustomer_address((String)map.get("customer_address"));
					customerVO.setCustomer_contact((String)map.get("customer_contact"));
					customerVO.setGroup_id((String)map.get("group_id"));
					customerVO.setIs_group((String)map.get("is_group"));
					customerVO.setIs_group_main((String)map.get("is_group_main"));
					customerVO.setOtp_status((String)map.get("otp_status"));
					customerVO.setOtp_status_msg(otpStatus.get((String)map.get("otp_status")));
					customerVO.setMailarea((String)map.get("MailArea"));
					customerVO.setMailbarcode((String)map.get("MailBarCode"));
					dam = new DataAccessManager();
					StringBuffer sql_sb = new StringBuffer();
					sql_sb.append("SELECT tc.token_esn,tc.related_to_time  \n");
					sql_sb.append("FROM   customers c  \n");
					sql_sb.append("       INNER JOIN token_customer tc  \n");
					if(map.get("is_group").equals("0")){
						sql_sb.append("               ON c.customer_id = tc.related_to  \n");
					}else if(map.get("is_group").equals("1")){
						sql_sb.append("               ON c.group_id = tc.related_to  \n");
					}
					sql_sb.append("WHERE  c.customer_id = :custId  \n");
					dam.setQueryString(sql_sb.toString());
					dam.setObject("custId", customer_id);
					List<Map<String,Object>> tokenList = dam.exeQueryMap();
					if(!tokenList.isEmpty()){
						customerVO.setToken_esn((String)tokenList.get(0).get("token_esn"));
						customerVO.setRelated_to_time(tokenList.get(0).get("related_to_time") != null?df.format((Timestamp)tokenList.get(0).get("related_to_time")):"");
					}else{
						customerVO.setToken_esn(null);
						// 查看此客戶ID是否申請Token中
//						String error_msg = chkJobQs(customer_id);
//						if(StringUtils.isNotBlank(error_msg)){
//							customerVO.setToken_status(false);
//						}else{
//							customerVO.setToken_status(true);
//						}
						if(((String) map.get("is_group")).equals("0")){
							customerVO.setToken_status(chkJobQs(customer_id));
						}else{
							customerVO.setToken_status(chkJobQs((String)map.get("group_id")));
						}
					}
//					customerVO.setCreated_time(map.get("created_time") != null?df.format((Timestamp)map.get("created_time")):"");
//					customerVO.setUpdated_time(map.get("updated_time") != null?df.format((Timestamp)map.get("updated_time")):"");
//					String is_group = StringUtils.trimToEmpty((String)map.get("is_group"));
//					String otp_status = StringUtils.trimToEmpty((String)map.get("otp_status"));
//					String token_esn = StringUtils.trimToEmpty((String)map.get("token_esn"));
//					customerVO.setToken_status(is_group.equals("0")&&otp_status.equals("00")&&token_esn.equals(""));
					customerVO.setToken_apply(chkTokenApply(customer_id));
					customerList.add(customerVO);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customerList;
	}
	
	public List<CustomerVO> getCustomerQuery(String customer_id){
		DataAccessManager dam = new DataAccessManager();
		List<CustomerVO> customerList = new ArrayList<CustomerVO>();
		
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
//			String sql = "select  Customers.customer_id,Customers.customer_name,Customers.AO_code,case  Customers.is_group   when '0' then '否' else '是' end is_group,case  Customers.is_group_main   when '0' then '否' else '是' end is_group_main,case Tokens.token_status  when '00' then '未啟用' when '01' then '啟用' when '02' then '暫禁' when '03' then '註銷' when '04' then '覆核中' when '05' then '覆核中' else '' end  tokenStatus,tokens.token_esn,tokens.updated_time from Customers left join Token_Customer on ( customers.customer_id = Token_Customer.related_to or customers.group_id = Token_Customer.related_to) left join Tokens on Token_Customer.token_esn = tokens.token_esn where customer_id = :customer_id or group_id = (select group_id from Customers where customer_id = :customer_id )Order by Customers.is_group_main DESC";
			StringBuffer sql_str = new StringBuffer();
			sql_str.append("select Customers.customer_id,Customers.customer_name,Customers.AO_code, ");
			sql_str.append("case Customers.is_group when '0' then '否' else '是' end is_group, ");
			sql_str.append("case Customers.is_group_main   when '0' then '否' else '是' end is_group_main, ");
			sql_str.append("case When isnull(Logs.Token_status,'') <> '' then Logs.Token_status ");
			sql_str.append("when Tokens.token_status ='00' then '未啟用' ");
			sql_str.append("when Tokens.token_status ='01' then '啟用' ");
			sql_str.append("when Tokens.token_status ='02' then '暫禁' ");
			sql_str.append("when Tokens.token_status = '03' then '註銷' ");
			sql_str.append("when Tokens.token_status= '04' then '覆核中' ");			
			sql_str.append("when Tokens.token_status = '05' then '覆核中' else '' end  tokenStatus, ");
			sql_str.append("Case When isnull(Logs.Token_status,'') <> '' then Logs.token_esn else  tokens.token_esn end token_esn, ");
			sql_str.append("tokens.updated_time,Customers.is_group_main ");
			sql_str.append("from Customers left join ( select token_esn,case when ISNULL(group_id,'') = '' then customer_id else group_id end related_to ");
			sql_str.append("from dbo.Job_queues union select token_esn,related_to from  dbo.Token_Customer)Token_Customer ");
			sql_str.append("on ( customers.customer_id = Token_Customer.related_to or customers.group_id   = Token_Customer.related_to ) ");
			sql_str.append("left join Tokens on Token_Customer.token_esn = tokens.token_esn ");
			sql_str.append("left join ( select top 1  Case  When job_code ='1004' then '03-註銷' else '' end Token_status ,customer_id,token_esn from Logs where  customer_id = :customer_id order by log_time desc ) Logs ");
			sql_str.append("on Logs.customer_id = Customers.customer_id where Customers.customer_id = :customer_id or ");
			sql_str.append("Customers.group_id = (select group_id from Customers where customer_id = :customer_id ) ");
			sql_str.append("Order by Customers.is_group_main DESC");
			dam.setObject("customer_id", customer_id);
			dam.setQueryString(sql_str.toString());
			List<Map<String,Object>> list = dam.exeQueryMap();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					CustomerVO customerVO = new CustomerVO();
					customerVO.setCustomer_id((String)map.get("customer_id"));				
					customerVO.setCustomer_name((String)map.get("customer_name"));
					customerVO.setAo_code((String)map.get("AO_code"));
					customerVO.setIs_group((String)map.get("is_group"));
					customerVO.setIs_group_main((String)map.get("is_group_main"));
					customerVO.setUpdated_time(map.get("updated_time") != null?df.format((Timestamp)map.get("updated_time")):"");
					customerVO.setToken_esn((String)map.get("token_esn"));
					customerVO.setTokenStatus((String)map.get("tokenStatus"));
					customerList.add(customerVO);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customerList;
	}
	/**
	 * 修改客戶資料
	 * @param customer_id
	 * @param name
	 * @param phone
	 * @param address
	 * @return
	 */
	public Boolean modifyData(String customer_id,String name,String phone,String address,String mailArea,String mailBarCode){
		boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "update Customers set customer_contact = :name, " +
				     "customer_phone = :phone, " +
				     "customer_address = :address, " +
				     "MailArea = :mailArea, " +
				     "MailBarCode = :mailBarCode " +
				     "where customer_id = :customer_id ";
		dam.setQueryString(sql);
		dam.setObject("name", name);
		dam.setObject("phone", phone);
		dam.setObject("address", address);
		dam.setObject("mailArea", mailArea);
		dam.setObject("mailBarCode", mailBarCode);
		dam.setObject("customer_id", customer_id);
		try {
			flag = dam.exeUpdate()>0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public String chkToken(String customer_id,String token_esn){
		String message = "";
		DataAccessManager dam = new DataAccessManager();
		StringBuffer sql_sb = new StringBuffer();
		sql_sb.append("select j.job_code, ");
		sql_sb.append("       \"error_msg\" = case ");
		sql_sb.append("                       when tc.related_to is not null then ( '已配發給其他客戶' ) ");
		sql_sb.append("                       when j.job_code = '1000' then ( ");
		sql_sb.append("                       '正被客戶ID: ' + j.customer_id + ' 申請，等待主管覆核中' ) ");
		sql_sb.append("                       when t.token_status = '03' then '已註銷' ");
		sql_sb.append("                       when j.job_code is null then '' ");
		sql_sb.append("                     end ");
		sql_sb.append("from   tokens as t ");
		sql_sb.append("       left join token_customer as tc ");
		sql_sb.append("              on t.token_esn = tc.token_esn ");
		sql_sb.append("       left join job_queues as j ");
		sql_sb.append("              on t.token_esn = j.token_esn ");
		sql_sb.append("where  t.token_esn = :token_esn ");
		sql_sb.append("       and ( tc.related_to is null ");
		sql_sb.append("              or tc.related_to is not null ) ");
		dam.setQueryString(sql_sb.toString());
		dam.setObject("token_esn", token_esn);
		try {
			List<Map<String,Object>> list = dam.exeQueryMap();
			if(!list.isEmpty()){
				return message = (String)list.get(0).get("error_msg");
			}else{
				return message = "不存在";
			}
		} catch (SQLException e) {
			logger.debug("客戶申請-配發TOKEN確認狀態SQL錯誤");
			e.printStackTrace();
		}
		return message;
	}
	
//	/**
//	 * 確認TOKEN是否能夠配發
//	 * true:能配發,false:TOKEN不能配發
//	 * @param customer_id
//	 * @param token_esn
//	 * @return
//	 */
//	public Boolean chkToken(String customer_id,String token_esn){
//		boolean flag = false;
//		DataAccessManager dam = new DataAccessManager();
//		StringBuffer sql_sb = new StringBuffer();
//		sql_sb.append("SELECT *  \n");
//		sql_sb.append("FROM   tokens t  \n");
//		sql_sb.append("       LEFT JOIN token_customer tc  \n");
//		sql_sb.append("              ON t.token_esn = tc.token_esn  \n");
//		sql_sb.append("WHERE  t.token_esn = :token_esn  \n");
//		sql_sb.append("       AND t.token_status = '00'  \n");
//		sql_sb.append("       AND tc.related_to IS NULL  \n");
//		
//		dam.setQueryString(sql_sb.toString());
//		dam.setObject("token_esn", token_esn);
//		try {
//			List<Map<String,Object>> tokenList = dam.exeQuery();
//			if(!tokenList.isEmpty()){
//				sql_sb = new StringBuffer();
//				dam = new DataAccessManager();
//				sql_sb.append("SELECT *  \n");
//				sql_sb.append("FROM   customers  \n");
//				sql_sb.append("WHERE  customer_id = :custId   \n");
//				dam.setQueryString(sql_sb.toString());
//				dam.setObject("custId", customer_id);
////				List<Map<String,Object>> custList = dam.exeQuery();
////				Map<String,Object> map = custList.get(0);
//				sql_sb = new StringBuffer();
//				dam = new DataAccessManager();
//				sql_sb.append("UPDATE customers  \n");
//				sql_sb.append("SET    updated_time = Getdate()  \n");
//				sql_sb.append("WHERE  customer_id = :custId  \n");
////				sql_sb.append("INSERT INTO token_customer  \n");
////				sql_sb.append("            (token_esn,  \n");
////				sql_sb.append("             related_to,  \n");
////				sql_sb.append("             is_related_to_group)  \n");
////				sql_sb.append("VALUES      (:token_esn,  \n");
////				sql_sb.append("             :related_to,  \n");
////				sql_sb.append("             :is_group)  \n");
//				dam.setObject("custId", customer_id);
////				dam.setObject("token_esn", token_esn);
////				if(map.get("is_group").equals("0")){
////					dam.setObject("related_to", customer_id);
////					dam.setObject("is_group", "0");
////				}else if(map.get("is_group").equals("1") && map.get("is_group_main").equals("1")){
////					dam.setObject("related_to", map.get("group_id"));
////					dam.setObject("is_group", "1");
////				}else{
////					return false;
////				}
//				dam.setQueryString(sql_sb.toString());
//				flag = dam.exeUpdate()>0;
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return flag;
//	}
	
//	/**
//	 * 寫入Token_Customer
//	 * @param customer_id
//	 * @param name
//	 * @param phone
//	 * @param address
//	 * @return
//	 */
//	public Boolean insertTokenCus(List<CustomerVO> customerList,String token_esn){
//		boolean flag = false;
//		DataAccessManager dam = new DataAccessManager();
//		String sql = "INSERT INTO Token_Customer(token_esn, related_to, is_related_to_group) " +
//	     "VALUES(:token_esn, :customer_id, :is_group)";
//		dam.setQueryString(sql);
//		dam.setObject("token_esn", token_esn);
//		dam.setObject("customer_id", customerList.get(0).getCustomer_id());
//		dam.setObject("is_group", customerList.get(0).getIs_group());
//		try {
//			System.out.println("flag=" + flag);
//			flag = dam.exeUpdate()>0;
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return flag;
//	}
	
//	/**
//	 * 確認客戶是否有申請Token
//	 * true:申請中, false:新申請
//	 */
//	public String chkJobQs(String custId){
//		String message = "";
//		DataAccessManager dam = new DataAccessManager();
//		String sql = "select * from Job_queues " +
//					 "where customer_id = :customer_id";  
//		dam.setQueryString(sql);
//		dam.setObject("customer_id", custId);
//		try {
//			List<Map<String,Object>> list = dam.exeQueryMap();
//			if(!list.isEmpty()){
//				return message = "該客戶申請TOKEN配發中..."; 
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}		
//		return message;
//	}
	
	/**
	 * 確認客戶是否有申請Token
	 * true:申請中, false:新申請
	 */
	public Boolean chkJobQs(String id){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "select * from Job_queues " +
					 "where customer_id = :id or group_id = :id"; 
		dam.setQueryString(sql);
		dam.setObject("id", id);
		try {
			List<Map<String,Object>> list = dam.exeQueryMap();
			if(!list.isEmpty()){
				flag = true; 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return flag;
	}
	
	/**
	 * 寫入Job_queues
	 * @param customer_id
	 * @param name
	 * @param phone
	 * @param address
	 * @return
	 */
	public String insertJobQs(List<CustomerVO> customerList,String token_esn,String userName,Date date){
//		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//		String dateString = sdf.format(date);
		String subdate = df.format(date);
//		System.out.println(dateString);
		boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "INSERT INTO Job_queues(job_id, job_code, customer_id, "+ 
					 "group_id, token_esn, token_status, operator, submitted_time) " +
	                 "VALUES(:job_id, :job_code, :customer_id, "+ 
					 ":group_id, :token_esn, :token_status, :operator, :submitted_time)";
		dam.setQueryString(sql);
		dam.setObject("job_id", IdUtil.getJob_id());
		dam.setObject("job_code", "1000");
		dam.setObject("customer_id", customerList.get(0).getCustomer_id());
		dam.setObject("group_id", customerList.get(0).getGroup_id());
		dam.setObject("token_esn", token_esn);
		dam.setObject("token_status", "00");
		dam.setObject("operator", userName);
		dam.setObject("submitted_time", subdate);
		try {
			flag = dam.exeUpdate()>0;
			if(flag){
				updateTokens(token_esn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return subdate;
	}
	
	/**
	 * update Tokens 的狀態為05(配發中,待覆核)
	 */
	public void updateTokens(String token_esn){
		DataAccessManager dam = new DataAccessManager();
		String sql = "UPDATE Tokens SET token_status ='05' WHERE token_esn = :token_esn ";
		dam.setObject("token_esn", token_esn);
		dam.setQueryString(sql);
		try {
			dam.exeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * TOKEN配發確認
	 * true:可以配發, false:不能申請配發
	 */
	public Boolean chkTokenApply(String custId){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "select * from Customers as a " +
					 "inner join Groups as b on a.group_id = b.group_id " +
					 "and a.customer_id = b.main_customer_id " +
					 "where a.customer_id = :customer_id " +
					 "and a.otp_status != '01' ";
		dam.setQueryString(sql);
		dam.setObject("customer_id", custId);
		try {
			List<Map<String,Object>> list = dam.exeQueryMap();
			if(!list.isEmpty()){
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public List<CustomerVO> getGroupList(String customer_id){
		List<CustomerVO> result = new ArrayList<CustomerVO>();
//		CustomerVO customerVO = new CustomerVO();
		DataAccessManager dam = new DataAccessManager();
		String sql = "select customer_id,AO_code,customer_name from Customers " +
					 "where group_id = (select group_id from Customers where customer_id = :customer_id) " +
					 "and is_group = '1' and customer_id != :customer_id";
		dam.setQueryString(sql);
		dam.setObject("customer_id", customer_id);
		try {
			List<Map<String,Object>> list = dam.exeQueryMap();
			if(!list.isEmpty()){
				for(Map<String,Object> map:list){
					CustomerVO customerVO = new CustomerVO();
					customerVO.setCustomer_id((String)map.get("customer_id"));
					// demoReport專用
					String strId = map.get("customer_id").toString();
					String demoId = "";
					if(strId.replaceAll(new String(" "),new String("")).length() < 13){
						demoId = strId.replace(strId.substring(2,18), "XXXXX");
					}else{
						demoId = strId.replace(strId.substring(4,18), "XXXXX");
					}
					customerVO.setCustomer_demo_id(demoId);
					
					customerVO.setAo_code((String)map.get("AO_code"));
					customerVO.setCustomer_name((String)map.get("customer_name"));
					result.add(customerVO);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public void getReport(String custId,String token_esn,String userId,List<ReportVO> reportList,Date date){
		ReportVO vo = new ReportVO();
		vo.setCustomerList(getCustomerForm(custId));
		vo.setGroupList(getGroupList(custId));
		vo.setSubdate(insertJobQs(vo.getCustomerList(), token_esn, userId,date));
		vo.setTokenEsn(token_esn);
		vo.setCustId(custId);
		reportList.add(vo);
		updateTokensDate(token_esn, vo.getSubdate());
//		generatePDF(reportList);		
	}
	
	public void getReport(String custId,String token_esn,String userId,List<ReportVO> reportList){
		Date date = new Date();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String subdate = df.format(date);
		ReportVO vo = new ReportVO();
		vo.setCustomerList(getCustomerForm(custId));
		vo.setGroupList(getGroupList(custId));
		vo.setSubdate(subdate);
		vo.setTokenEsn(token_esn);
		vo.setCustId(custId);
		reportList.add(vo);	
		updateTokensDate(token_esn, vo.getSubdate());
	}
	
	public void deleteDB(String subdate){
		try {
			DataAccessManager dam = new DataAccessManager();
			StringBuffer sb = new StringBuffer();
			sb.append("update Tokens set token_status = '00' ");
			sb.append("where token_esn IN (select token_esn from Job_queues where submitted_time = :submitted_time );");
			sb.append("delete from Job_queues where submitted_time = :submitted_time ");
			dam.setQueryString(sb.toString());
			dam.setObject("submitted_time", subdate);
			dam.exeDelete();
		} catch (SQLException e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
	}
	
	/**
	 * update Tokens Date
	 */
	public void updateTokensDate(String token_esn, String subdate){
		DataAccessManager dam = new DataAccessManager();
		String sql = "UPDATE Tokens SET receipt_printed_time = :receipt_printed_time, is_receipt_printed = '1' WHERE token_esn = :token_esn";
		dam.setObject("token_esn", token_esn);
		dam.setObject("receipt_printed_time", subdate);
		dam.setQueryString(sql);
		try {
			dam.exeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 產生PDF檔
	 */
	public String generatePDF(List<ReportVO> reportList){
		String path = "";
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		StringBuffer sb2 = new StringBuffer(sdf2.format(new Date()));
		Document document = new Document(PageSize.A4, 50, 50, 40, 40); // 左右上下
		try {       
			System.out.println("generatePDF:");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
			StringBuffer dateStr = new StringBuffer(sdf.format(new Date()));
			String filePath = PropsUtil.getConfig("pdfFile.path");
			path = filePath + dateStr.toString() + ".pdf";
			System.out.println("filePath:" + path);
            PdfWriter.getInstance(document,new FileOutputStream(path)); 
            document.open();
  

            BaseFont bfChinese = BaseFont.createFont("C:\\WINDOWS\\Fonts\\mingliu.ttc,1", BaseFont.IDENTITY_H,  BaseFont.NOT_EMBEDDED);
            Font csfont = new Font(bfChinese, 10, Font.BOLD);
            Font csfont2 = new Font(bfChinese, 10, Font.UNDERLINE);
            Font whitefont = new Font(bfChinese, 10, Font.BOLD);
            whitefont.setColor(255, 255, 255);
            Font font = new Font(Font.TIMES_ROMAN, 10,Font.BOLD);
            for(int index = 0;index<reportList.size();index++){
            	Paragraph preface = new Paragraph("地址：香港九龍尖沙咀北京道1號18樓    永豐銀行\n FAX(852)2907-6828\n", csfont);
            	preface.setAlignment(Element.ALIGN_RIGHT);
            	addEmptyLine(preface, 3);
            	document.add(preface);
            	
            	String custName = reportList.get(index).getCustomerList().get(0).getCustomer_name();
            	String custAddr = reportList.get(index).getCustomerList().get(0).getCustomer_address();
            	String custCont = reportList.get(index).getCustomerList().get(0).getCustomer_contact();
            	Table custTable = new Table(2);
            	int[] width = {99,1};
            	custTable.setWidths(width);
            	custTable.setBorderColor(Color.WHITE);
            	custTable.setWidth(50);
            	custTable.setAlignment(Element.ALIGN_LEFT);
            	custTable.addCell(createCell(custName, csfont));
            	custTable.addCell(createCell(".", whitefont));//設定高度
            	Cell addrCell = createCell(custAddr, csfont);
            	addrCell.setMaxLines(3);
            	custTable.addCell(addrCell);
            	custTable.addCell(createCell(".\n.\n.", whitefont));//設定高度
            	custTable.addCell(createCell(custCont, csfont));
            	custTable.addCell(createCell(".", whitefont));
            	custTable.addCell(colCell(".", whitefont,2));
            	custTable.addCell(colCell(".", whitefont,2));
            	custTable.addCell(colCell(".", whitefont,2));
            	custTable.addCell(colCell(".", whitefont,2));
            	
//            	preface = new Paragraph(reportList.get(index).getCustomerList().get(0).getCustomer_name()+"\n"+
//            			reportList.get(index).getCustomerList().get(0).getCustomer_address()+"\n"+
//            			reportList.get(index).getCustomerList().get(0).getCustomer_contact(), csfont);
//            	preface.setAlignment(Element.ALIGN_LEFT);
//            	document.add(preface);
//            	addEmptyLine(preface, 5);
            	document.add(custTable);
            	
            	preface = new Paragraph("親愛的客戶 您好，\n 有關 貴客戶於本行申請動態密碼載具帳戶資料明細如下：\n"+
            			"Token ID  "+reportList.get(index).getTokenEsn()+"\n"+
            			"客戶 ID  "+reportList.get(index).getCustomerList().get(0).getCustomer_demo_id(), csfont);
            	preface.setAlignment(Element.ALIGN_LEFT);
            	document.add(preface);
            	//表格設定
            	Table table = new Table(5);
//            	float[] widths = {1f,1f,1f,1f,1f};
//            	PdfPTable table = new PdfPTable(widths);
            	table.setWidth(100);
            	table.setBorderColor(Color.WHITE);
            	table.setAlignment(Element.ALIGN_LEFT);
            	int[] widths = {20,20,20,20,20};
            	table.setWidths(widths);
            	Cell cell = createCell("客戶綁定 ID  ",csfont);
            	cell.setRowspan(5);
            	table.addCell(cell);
//            	StringBuffer sb = new StringBuffer("客戶綁定 ID  ");
            	
            	for(int i=0;i<reportList.get(index).getGroupList().size();i++){ 
            		String text = reportList.get(index).getGroupList().get(i).getCustomer_demo_id();
            		table.addCell(createCell(text,csfont));
            	}
            	for(int i=0;i<20-reportList.get(index).getGroupList().size();i++){
            		table.addCell(createCell("、",whitefont));
            	}
//            	sb.append(table);
//            	for(int i=0;i<reportList.get(index).getGroupList().size();i++){ 
//            		sb.append(reportList.get(index).getGroupList().get(i).getCustomer_demo_id()+" ");
//            		if(i==4){
//            			sb.append("\r\n");
//            		}	
//            	}
//            	preface = new Paragraph(sb.toString(), csfont);
//            	preface.setAlignment(Element.ALIGN_LEFT);
            	document.add(table);
//            	addEmptyLine(preface, 2);
//            	document.add(preface);
            	
            	preface = new Paragraph("---------------------------------------------------------------------------------------------------------------------------");
            	preface.setAlignment(Element.ALIGN_CENTER);
            	addEmptyLine(preface, 1);
            	document.add(preface);
            	
            	preface = new Paragraph("動 態 密 碼 載 具 簽 收 回 函", csfont2);
            	preface.setAlignment(Element.ALIGN_CENTER);
            	document.add(preface);
            	preface = new Paragraph("ONE TIME PASSWORD TOKEN RECEIPT", font);
            	preface.setAlignment(Element.ALIGN_CENTER);
            	addEmptyLine(preface, 1);
            	document.add(preface);
            	
            	preface = new Paragraph("本公司/本人已於______年_____月_____日接獲  貴行「動態密碼載具」並確認前開「動態密碼載具」外觀無損，惠\n" +
            			"請  貴行於確認簽章字樣無誤後，開啟本公司/本人動態密碼載具驗證傳真指示密碼之機制，特此證明。\n"+
            			"The Company has / I have received the One Time Password Token ('Token') on _________________________ (the date) issued\n" +
            			"by Bank SinoPac Hong Kong Branch ('Bank') and confirmed the said Token is undamaged.The company/I hereby authorize the\n" +
            			"Bank to activate the Token as the authentication measure for fax instruction after the Bank verifies the Signature of this receipt.\n 此致\n" +
            			"This Receipt is made to\n 永豐商業銀行股份有限公司香港分行\n Bank SinoPac Hong Kong Branch", csfont);
            	preface.setAlignment(Element.ALIGN_LEFT);
            	addEmptyLine(preface,4);
            	document.add(preface);
            	
            	preface = new Paragraph("_______________________________________________________________\n" +
            										"個人戶親簽/公司戶請蓋留存印鑑 Authorized Signature (and chop if any)", csfont);
            	preface.setAlignment(Element.ALIGN_RIGHT);
            	addEmptyLine(preface, 1);
            	document.add(preface);
            	
            	preface = new Paragraph("請將本函寄回/傳真至本分行", csfont);
            	preface.setAlignment(Element.ALIGN_LEFT);
            	document.add(preface);
            	preface = new Paragraph(sb2.toString(), csfont);
            	preface.setAlignment(Element.ALIGN_RIGHT);
            	document.add(preface);
            	if(index < reportList.size()-1){
            		document.newPage();
            	}
//            Font font = FontFactory.getFont(FontFactory.HELVETICA,18, Font.BOLDITALIC, new BaseColor(0, 0, 255));
//            Font font = new Font(Font.FontFamily.TIMES_ROMAN, 18,Font.BOLD);
//            preface.add(new Paragraph("Title of the document", font));
//            
//            addEmptyLine(preface, 1);
//            preface.add(new Paragraph("Content of the document", font));
//            preface.setAlignment(Element.ALIGN_RIGHT);
//            document.add(preface);
            }
            document.close();

		}catch (Exception e) {
		      e.printStackTrace();
		      File file = new File(path);
		      if(file.isFile()){
		    	  file.deleteOnExit();
		      }
	    }
		return path;

	}
	
	private static void addEmptyLine(Paragraph paragraph, int number) {
	    for (int i = 0; i < number; i++) {
	      paragraph.add(new Paragraph(" "));
	    }
	}
	
	private Cell colCell(String text, Font font,int colspan){
		Cell cell = new Cell();
		try {
			cell.setBorder(Rectangle.NO_BORDER);
			cell.addElement(new Phrase(text, font));
			if(colspan>1)
				cell.setColspan(colspan);
		} catch (BadElementException e) {
			e.printStackTrace();
		}
		return cell;
	}
	
	private Cell createCell(String text, Font font) {
		
		Cell cell = new Cell();
		try {
			cell.setBorderColor(Color.WHITE);
			cell.addElement(new Phrase(text, font));
			cell.setVerticalAlignment(Element.ALIGN_BASELINE);
		} catch (BadElementException e) {
			e.printStackTrace();
		}
		return cell;
	}
	
//	public String getToken_status(String token_esn){
//		String result = "不存在";
//		DataAccessManager dam = new DataAccessManager();
//		String sql = "select * from Tokens where token_esn = :token_esn";
//		dam.setQueryString(sql);
//		dam.setObject("token_esn", token_esn);
//		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
//		try {
//			list = dam.exeQuery();
//			if(!list.isEmpty()){
//				for(Map<String,Object> map:list){
//					String token_status = (String)map.get("token_status");
//					if(StringUtils.equals(token_status, "03")){
//						result = "已註銷";
//					}
//				}
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return result;
//	}
}
